package com.yuhf.dbutils;

import com.yuhf.dbutils.exception.AccessDbException;
import com.yuhf.dbutils.iface.RowMapper;
import lombok.extern.log4j.Log4j;

import java.sql.*;
import java.util.*;
import java.util.stream.IntStream;

/**
 * @author yuhf
 */
@Log4j
public class JdbcTemplate {

    public static int getTotalRow(String sql,Object...params){
        var totalRow = 0;
        ResultSet rs = null;
        Connection connection = null;
        try {
            connection = DBConnection.getConnection();      //获取数据库连接
            PreparedStatement psmt = connection.prepareStatement(sql);  //创建预编译语句
            if(null!=params && params.length!=0 && psmt.getParameterMetaData().getParameterCount()==params.length){
                IntStream.iterate(0,i->i+1).limit(params.length).forEach(i->{
                    try {
                        psmt.setObject(i+1,params[i]);
                    } catch (SQLException e) {
                        log.error("query parameter error,message is " + e.getMessage());
                        throw new AccessDbException("查询参数设置错误");
                    }
                });
            }
            rs=psmt.executeQuery();
            if(rs.next()){
                totalRow = rs.getInt(1);
            }
        } catch (SQLException e) {
            log.error("query error,message is " + e.getMessage());
            throw new AccessDbException("查询操作失败");
        } finally{
            try {
                if(connection.getAutoCommit()){
                    DBConnection.closeConnection();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return totalRow;
    }

    public static int insertByGenerateKey(String sql,Object...params){
        int id=0;
        Connection connection = null;
        try{
            connection = DBConnection.getConnection();
            PreparedStatement psmt = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            if(null!=params && params.length!=0 && psmt.getParameterMetaData().getParameterCount()==params.length){
                IntStream.iterate(0,i->i+1).limit(params.length).forEach(i->{
                    try {
                        psmt.setObject(i+1,params[i]);
                    } catch (SQLException e) {
                        log.error("query parameter error,message is " + e.getMessage());
                        throw new AccessDbException("查询参数设置错误");
                    }
                });
            }
            int totalRow = psmt.executeUpdate();
            ResultSet rs=psmt.getGeneratedKeys();
            if(rs.next()){
                id=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try {
                if(connection.getAutoCommit()){
                    DBConnection.closeConnection();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return id;
    }

    public static <T> List<T> query(String sql, RowMapper<T> rsh, Object...params){
        List<T> list = new ArrayList();
        ResultSet rs = null;
        Connection connection = null;
        try {
            connection = DBConnection.getConnection();  //获取数据库连接，该连接是基于线程绑定的。
            PreparedStatement psmt = connection.prepareStatement(sql);
            //设置查询参数
            if(null!=params && params.length!=0 && psmt.getParameterMetaData().getParameterCount()==params.length){
                IntStream.iterate(0,i->i+1).limit(params.length).forEach(i->{
                    try {
                        psmt.setObject(i+1,params[i]);  //设置单项的查询参数，由于使用了setObject方法，所以可以传入任何类型的参数。
                    } catch (SQLException e) {
                        log.error("query parameter error,message is " + e.getMessage());
                        throw new AccessDbException("查询参数设置错误");
                    }
                });
            }
            rs=psmt.executeQuery(); //执行查询操作
            while(rs.next()){            //处理查询结果集，将结果集转换为指定类型的对象列表
                T value=rsh.handler(rs);    //调用RowMapper接口的handler方法，将当前行的结果集转换为指定类型的对象。
                list.add(value);    //将转换后的对象添加到列表中。
            }
        } catch (SQLException e) {
            log.error("query error,message is " + e.getMessage());
            throw new AccessDbException("查询操作失败");
        } finally{
            DBConnection.closeConnection(); //关闭数据库连接，该连接也是基于线程绑定的，由此可以确定关闭一定是当前线程中已经打开的Connection实例。
        }
        return list;
    }
    public static List<Map<String,Object>> query(String sql, Object ...params){
        List<Map<String,Object>> list = new ArrayList();
        ResultSet rs = null;
        Connection connection = null;
        try {
            connection = DBConnection.getConnection();
            PreparedStatement psmt = connection.prepareStatement(sql);
            if(null!=params && params.length!=0 && psmt.getParameterMetaData().getParameterCount()==params.length){
                IntStream.iterate(0,i->i+1).limit(params.length).forEach(i->{
                    try {
                        psmt.setObject(i+1,params[i]);
                    } catch (SQLException e) {
                        log.error("query parameter error,message is " + e.getMessage());
                        throw new AccessDbException("查询参数设置错误");
                    }
                });
            }
            rs=psmt.executeQuery();
            while(rs.next()){   //将查询结果集中的每一行数据转换为Map对象，并将Map对象添加到列表中。
                Map<String,Object> row = new HashMap();
                ResultSetMetaData rmd = rs.getMetaData();   //获取结果集的元数据，包括列名、列类型等信息。
                for(int i=0,len=rmd.getColumnCount();i<len;i++){
                    row.put(rmd.getColumnLabel(i+1),rs.getObject(i+1)); //将每一列的值作为Map对象的键值对存储到Map对象中。
                }
                list.add(row);
            }
        } catch (SQLException e) {
            log.error("query error,message is " + e.getMessage());
            throw new AccessDbException("查询操作失败");
        } finally{
            try {
                if(connection.getAutoCommit()){
                    DBConnection.closeConnection();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return list;
    }
    public static int update(String sql,Object ...params){
        var totalRow = 0;
        Connection connection = null;
        try {
            connection = DBConnection.getConnection();
            PreparedStatement psmt = connection.prepareStatement(sql);
            if(null!=params && params.length!=0 &&psmt.getParameterMetaData().getParameterCount()==params.length){
                IntStream.range(0,params.length).forEach(i->{
                    try {
                        psmt.setObject(i+1,params[i]);
                    } catch (SQLException e) {
                        log.error("update parameter error,message is "+e.getMessage());
                        throw new AccessDbException("增、删、改操作的参数设置错误");
                    }
                });
            }
            totalRow = psmt.executeUpdate();
        } catch (SQLException e) {
            log.error("update operation error,message is "+e.getMessage());
            throw new AccessDbException("增、删、改操作失败"+e.getMessage());
        } finally{
            try {
                if(connection.getAutoCommit()){
                    DBConnection.closeConnection();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return totalRow;
    }
}
